/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Procs;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;


/**
 *
 * @author jorgeraulgrion
 */
public class ProcsTablero {
    java.sql.Connection con = null;    
    java.sql.ResultSet rs = null ;
    
    
    public  ProcsTablero(){
        try{
             con = DriverManager.getConnection("jdbc:mysql://localhost:3306/BDscrum","root","1234");
        // ip bufete.. con = DriverManager.getConnection("jdbc:mysql://10.101.5.154/bufete","bufete","bufete");

        }
        catch (SQLException ex){}
        
    }
    
    public java.sql.ResultSet verProyectos(int idsprint , int idusuario) throws SQLException{
        java.sql.ResultSet rs = null;
         Statement cs =  con.createStatement();
        rs = cs.executeQuery("select p.nombre, p.idProyecto, r.nombre as rn from Proyecto as p inner join rolproyecto as rp on p.idProyecto = rp.Proyecto_idProyecto"
                 + " inner join rol as r on r.idrol=rp.rol_idrol "
                 + " where rp.Usuario_idUsuario = "+idusuario+" and r.nombre='Scrum Master'" );
         return rs; 
    }
    
    public java.sql.ResultSet verUsuarios() throws SQLException{
        java.sql.ResultSet rs = null;
         Statement cs =  con.createStatement();
         rs = cs.executeQuery("select idUsuario,nombre from Usuario " );
         return rs; 
    }
    
    public void InsertarTarea(String nombre, String descripcion, int creador, int idsprint, int idproyecto, String prioridad, int estado){
        try {
            //con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","");
            String bandera=null;
            java.sql.CallableStatement cs = con.prepareCall("{call InsertarTarea('"+nombre+"','"+descripcion+"',"+creador+","+idsprint+","+idproyecto+",'"+prioridad+"',"+estado+")}");
            java.sql.Statement stmt  = con.createStatement();
            java.sql.ResultSet rs = cs.executeQuery();
            
        } catch (SQLException ex) {
           
        }
    }
    
    public void ActualizaEstadoSprint(int idsprint){
        try {
            //con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","");
            String bandera=null;
            java.sql.CallableStatement cs = con.prepareCall("{call  ActualizaEstadoSprint("+idsprint+")}");
            java.sql.Statement stmt  = con.createStatement();
            java.sql.ResultSet rs = cs.executeQuery();
            
        } catch (SQLException ex) {
           
        }
    }
    
    public java.sql.ResultSet verEstadoSprint(int idsprint) throws SQLException{
        java.sql.ResultSet rs = null;
         Statement cs =  con.createStatement();
         rs = cs.executeQuery("select estado from sprint where idsprint = "+ "'"+idsprint +"'" );
         return rs; 
    }
    
    public void ActualizaEstadoTarea(int idtarea, int estado){
        try {
            //con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","");
            String bandera=null;
            java.sql.CallableStatement cs = con.prepareCall("{call  ActualizaEstadoTarea("+idtarea+","+estado+")}");
            java.sql.Statement stmt  = con.createStatement();
            java.sql.ResultSet rs = cs.executeQuery();
            
        } catch (SQLException ex) {
           
        }
    }
   
    public java.sql.ResultSet verdatosTarea() throws SQLException{
        java.sql.ResultSet rs = null;
         Statement cs =  con.createStatement();
         rs = cs.executeQuery("select t.idTArea, t.estado from sprint as s " +
                 "inner join Tarea as t on s.idsprint = t.sprint_idsprint;");
         return rs; 
    }
    
        public void Asignartarea_usuario(int idtarea, int iduser){
        try {
            //con = DriverManager.getConnection("jdbc:mysql://localhost/bufete","root","");
            String bandera=null;
            java.sql.CallableStatement cs = con.prepareCall("{AsignarTareaUsuario("+iduser+"',"+idtarea+")}");
            java.sql.Statement stmt  = con.createStatement();
            java.sql.ResultSet rs = cs.executeQuery();
            
        } catch (SQLException ex) {
           
        }
    }
    
}

